package etf.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Random;

import etf.library.Address;
import etf.library.Author;
import etf.library.Book;
import etf.library.Category;
import etf.library.Employee;
import etf.library.Member;

public class IntermediateJDBC {
	
	private Connection connection;
	
	public IntermediateJDBC(){
		connection = JDBCUtil.getConnection();
		JDBCUtil.prepareStatements(connection);
	}
	
	public long insert(int brojSlogova) {
		ResultSet generatedKeys = null;
		int addressID = -1;
		long elapsedTime = 0;
		try {
			connection.setAutoCommit(false);
			long startTime = System.currentTimeMillis();
			for (int i = 0; i < brojSlogova; i++) {
				Address address = new Address("Splitska" + i,i, "Sarajevo", i, "BiH");
				Employee emp = new Employee("John_"+i,"Doe_"+i,30,1500,address);
				JDBCUtil.setInsertAddress(address);
				
				generatedKeys = JDBCUtil.insertAddress.getGeneratedKeys();
				if (generatedKeys.next()) {
					addressID = generatedKeys.getInt(1);	
				}
				
				JDBCUtil.setInsertEmp(emp, addressID);
				
			}
			connection.commit();
			long stopTime = System.currentTimeMillis();
			elapsedTime = stopTime - startTime;

		} catch (Exception e) {
			e.printStackTrace();
		}
		return elapsedTime;
	}
	
	public long query(int size){
		String sql = "select e.first_name,e.last_name,e.age,e.salary from employee e, address a where e.address = a.id and a.house_number =?";
		return queryUtil(size, sql);
	}
	
	public long indexedQuery(int size){
		String sql = "select e.first_name,e.last_name,e.age,e.salary from employee e, address a where e.address = a.id and a.zip_post_code =?";
		return queryUtil(size, sql);
	}
	
	private long queryUtil(int size, String sql){
		long elapsedTime = 0;
		double suma = 0;
		PreparedStatement selectStatement = null;
		try {
			
			selectStatement = connection.prepareStatement(sql);
			
			for (int i = 0; i < 4; i++) {
				long startTime = System.currentTimeMillis();
				selectStatement.setInt(1, getRandom(size));
				ResultSet rs = selectStatement.executeQuery();
				if (rs.next()) {
					Employee emp = new Employee();
					emp.setFirstName(rs.getString("first_name"));
					emp.setLastName(rs.getString("last_name"));
					emp.setAge(rs.getInt("age"));
					emp.setSalary(rs.getInt("salary"));
					System.out.println(emp.getFirstName());
				}
				long stopTime = System.currentTimeMillis();
				elapsedTime = stopTime - startTime;
				suma +=elapsedTime;
			}
			suma =suma/4;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return (long) suma;
	}

	public long update(int size){
		long elapsedTime = 0;
		String sql = "update employee e, address a set e.first_name = 'New_name' where e.address = a.id and a.zip_post_code =?";
		PreparedStatement selectStatement = null;
		try {
			
			selectStatement = connection.prepareStatement(sql);
			long startTime = System.currentTimeMillis();

			selectStatement.setInt(1, getRandom(size));
			int rs = selectStatement.executeUpdate();
			System.out.println("jdbc:" + rs);
			
			long stopTime = System.currentTimeMillis();
			elapsedTime = stopTime - startTime;			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return elapsedTime;
	}
	
	public long delete(int size){
		String sql = "delete e from employee e, address a where e.address = a.id and a.house_number =?";
		return deleteUtil(sql, size);
	}
	
	public long indexedDelete(int size){
		String sql = "delete e from employee e, address a where e.address = a.id and a.zip_post_code =?";
		return deleteUtil(sql, size);
	}

	private long deleteUtil(String sql, int size) {
		long elapsedTime = 0;
		PreparedStatement deleteStatement = null;
		double suma = 0;
		try {
			deleteStatement = connection.prepareStatement(sql);
			
			for (int i = 0; i < 4; i++) {
				long startTime = System.currentTimeMillis();
				connection.setAutoCommit(false);
				deleteStatement.setInt(1, getRandom(size));
				deleteStatement.execute();
				connection.commit();
				long stopTime = System.currentTimeMillis();
				elapsedTime = stopTime - startTime;
				suma += elapsedTime;
			}
			suma = suma/4;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return (long)suma;
	}
	
	// -------------------------------------------------------------------------------------------
	//											1 to N
	//-------------------------------------------------------------------------------------------
	
	public long insert_1_to_N(int brojSlogova) {
		ResultSet genKeysAuthor = null;
		ResultSet genKeysCategory = null;
		int authorID = -1;
		int categoryID = -1;
		long elapsedTime = 0;
		try {
			
			long startTime = System.currentTimeMillis();
			connection.setAutoCommit(false);
			
			Category cat = new Category("Drama");
			
			JDBCUtil.setInsertCategory(cat);
			genKeysCategory = JDBCUtil.insertCategory.getGeneratedKeys();
			if (genKeysCategory.next()) {
				categoryID = genKeysCategory.getInt(1);	
			}
			
			for (int i = 0; i < brojSlogova/100; i++) {
				Author author = new Author("Peter_"+i, "Lacko_" +i, "Male", i);
				JDBCUtil.setInsertAuthor(author);
				genKeysAuthor = JDBCUtil.insertAuthor.getGeneratedKeys();
				if (genKeysAuthor.next()) {
					authorID = genKeysAuthor.getInt(1);	
				}
				for (int j = 0; j < 100; j++) {
					Book book = new Book("Book_" + j, i*100 + j, cat, author);
					JDBCUtil.setInsertBook(book, authorID, categoryID);
				}
			}
			connection.commit();
			long stopTime = System.currentTimeMillis();
			elapsedTime = stopTime - startTime;

		} catch (Exception e) {
			e.printStackTrace();
		}
		return elapsedTime;
	}
	
	public long query_1_to_N(int size){
		String sql = "select a.first_name,a.last_name,a.age,a.gender from author a, book b where a.id = b.author and b.year =?";
		long elapsedTime = 0;
		PreparedStatement selectStatement = null;
		try {
			
			long startTime = System.currentTimeMillis();
			connection.setAutoCommit(false);
			selectStatement = connection.prepareStatement(sql);
			selectStatement.setInt(1, getRandom(size));
			ResultSet rs = selectStatement.executeQuery();
			if (rs.next())
            {
				Author a = new Author();
				a.setFirstName(rs.getString("first_name"));
				a.setLastName(rs.getString("last_name"));
				a.setAge(rs.getInt("age"));
				a.setGender(rs.getString("gender"));
				System.out.println("A:"+ a.getFirstName());
            }
				
			long stopTime = System.currentTimeMillis();
			elapsedTime = stopTime - startTime;			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return elapsedTime;
	}
	
	public long update_1_to_N(int size){
		String sql = "update author a, book b set a.first_name = 'New_name' where a.id = b.author and b.year =?";
		long elapsedTime = 0;
		PreparedStatement selectStatement = null;
		try {
			connection.setAutoCommit(false);
			
			long startTime = System.currentTimeMillis();
			
			selectStatement = connection.prepareStatement(sql);
			selectStatement.setInt(1, getRandom(size));
			int rs = selectStatement.executeUpdate();
			System.out.println("jdbc:"+ rs);
				
			long stopTime = System.currentTimeMillis();
			elapsedTime = stopTime - startTime;			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return elapsedTime;
	}
	
	public long delete_1_to_N(int size){
		String sql = "delete b.* from author a, book b where a.id = b.author and b.year =?";
		long elapsedTime = 0;
		PreparedStatement selectStatement = null;
		try {
			connection.setAutoCommit(false);
			
			long startTime = System.currentTimeMillis();
			
			selectStatement = connection.prepareStatement(sql);
			selectStatement.setInt(1, getRandom(size));
			int rs = selectStatement.executeUpdate();
			System.out.println("jdbc:"+ rs);
				
			long stopTime = System.currentTimeMillis();
			elapsedTime = stopTime - startTime;			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return elapsedTime;
	}
	
	public long insert_N_to_N(int size) {

		ResultSet genKeysAuthor = null;
		ResultSet genKeysCategory = null;
		ResultSet genKeysBook = null;
		ResultSet genKeysMember = null;
		
		int authorID = -1;
		int categoryID = -1;
		int memberID = -1;
		int bookID = -1;
		long elapsedTime = 0;
		try {
			
			long startTime = System.currentTimeMillis();
			connection.setAutoCommit(false);
			Author author = new Author("Peter", "Lacko", "Male", 44);
			Category cat = new Category("Drama");
			
			JDBCUtil.setInsertAuthor(author);
			genKeysAuthor = JDBCUtil.insertAuthor.getGeneratedKeys();
			if (genKeysAuthor.next()) {
				authorID = genKeysAuthor.getInt(1);	
			}
			
			JDBCUtil.setInsertCategory(cat);
			genKeysCategory = JDBCUtil.insertCategory.getGeneratedKeys();
			if (genKeysCategory.next()) {
				categoryID = genKeysCategory.getInt(1);	
			}
			
			for (int j = 0; j < size/100; j++) {
				Member member = new Member("Mark_"+j, "Twain_"+j, j, "gmail");
				
				JDBCUtil.setInsertMember(member);
				genKeysMember = JDBCUtil.insertMember.getGeneratedKeys();
				if (genKeysMember.next()) {
					memberID = genKeysMember.getInt(1);	
				}
				for (int i = 0; i < 100; i++) {
					int temp = j*100+i;
					Book book = new Book("Book_"+temp, i, cat, author);

					JDBCUtil.setInsertBook(book, authorID, categoryID);
					genKeysBook = JDBCUtil.insertBook.getGeneratedKeys();
					if (genKeysBook.next()) {
						bookID = genKeysBook.getInt(1);	
					}
					// INSERT U TABELU ZA VEZU VISE NA VISE
					book.addRenter(member);
					member.rentABook(book);
					JDBCUtil.setInsertRentedBooks(memberID, bookID);

				}
			}
			connection.commit();
			long stopTime = System.currentTimeMillis();
			elapsedTime = stopTime - startTime;

		} catch (Exception e) {
			e.printStackTrace();
		}
		return elapsedTime;
	}
	
	public long query_N_to_N(int size){
		String sql = "select m.first_name,m.last_name,m.phone_number,m.email from member m,rented_books rb, book b " +
				"where m.id = rb.member_id and b.id = rb.book_id and b.year =?";
		long elapsedTime = 0;
		PreparedStatement selectStatement = null;
		try {
			
			selectStatement = connection.prepareStatement(sql);
			long startTime = System.currentTimeMillis();

			selectStatement.setInt(1, getRandom(100));
			ResultSet rs = selectStatement.executeQuery();
			System.out.println(222222222);
			if (rs.next())
            {
				Member m = new Member();
				m.setFirstName(rs.getString("first_name"));
				m.setLastName(rs.getString("last_name"));
				m.setPhoneNumber(rs.getInt("phone_number"));
				m.setEmail(rs.getString("email"));
				System.out.println("M:"+ m.getFirstName());
            }
				
			
			long stopTime = System.currentTimeMillis();
			elapsedTime = stopTime - startTime;			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return elapsedTime;
	}
	
	public long update_N_to_N(int size){
		String sql = "update member m,rented_books rb, book b set m.first_name = 'New_name' " +
				"where m.id = rb.member_id and b.id = rb.book_id and b.year =?";
		long elapsedTime = 0;
		PreparedStatement selectStatement = null;
		try {
			;
			long startTime = System.currentTimeMillis();
			connection.setAutoCommit(false);
			selectStatement = connection.prepareStatement(sql);
			selectStatement.setInt(1, getRandom(100));
			int rs = selectStatement.executeUpdate();
			System.out.println("M:"+ rs);
			connection.commit();
			long stopTime = System.currentTimeMillis();
			elapsedTime = stopTime - startTime;			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return elapsedTime;
	}
	
	public long delete_N_to_N(int size){
		String sql = "delete rb.* from rented_books rb, member m, book b " +
				"where m.id = rb.member_id and b.id = rb.book_id and b.title =?";
		long elapsedTime = 0;
		PreparedStatement selectStatement = null;
		try {
			;
			long startTime = System.currentTimeMillis();
			String bookTitle = "Book_"+getRandom(size);
			connection.setAutoCommit(false);
			selectStatement = connection.prepareStatement(sql);
			selectStatement.setString(1, bookTitle);
			int rs = selectStatement.executeUpdate();
			System.out.println("M:"+ rs);
			connection.commit();
			long stopTime = System.currentTimeMillis();
			elapsedTime = stopTime - startTime;			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return elapsedTime;
	}
	
	public void clear_1_on_1(){
		JDBCUtil.clearTable(connection,"employee");
		JDBCUtil.clearTable(connection,"address");
	}
	
	public void clear_1_on_N(){
		JDBCUtil.clearTable(connection,"author");
		JDBCUtil.clearTable(connection,"book");
	}
	
	public void clear_N_on_N(){
		JDBCUtil.clearTable(connection,"rented_books");
		JDBCUtil.clearTable(connection,"member");
		JDBCUtil.clearTable(connection,"book");
	}
	
	private int getRandom(int max){
		Random r = new Random();
        return r.nextInt(max); 
	}
}
